
导航器:跳至底部
总体目标:
具体目标:
Python代码如下:
import urllib.request,urllib.error
from bs4 import BeautifulSoup
import re
import pandas as pd
def main(): # 流程控制
baseurl="https://movie.douban.com/top250?start=" # 目标url的模板
dataList=getData(baseurl)
savepath="./source/top250.xlsx" # 采用相对路径保存
saveData(dataList,savepath)
def askUrl(url):
head={
"User-Agent":"Mozilla / 5.0(Windows NT 10.0; WOW64) AppleWebKit / 537.36(KHTML, like Gecko) Chrome / 86.0 .4240 .198 Safari / 537.36"
} # 模拟浏览器头部,使用用户代理信息
request=urllib.request.Request(url,headers=head) # 用Request封装url和header作为request对象
html=""
try: # 监控爬取异常
response=urllib.request.urlopen(request) # 通过request对象返回response对象
html=response.read().decode('utf-8') # 读取并解码
# print(html)
except Exception as result:
if hasattr(result,'code'):
print(result.code) # 异常包含code项时返回相关信息
if hasattr(result,'reason'):
print(result.reason) # 异常包含reason项时返回相关信息
return html
# 构建目标信息的正则表达式模板
# 详情链接:
findLink=re.compile(r'<a href="(.*?)">')
# 海报链接:
findImg=re.compile(r'<img.*src="(.*?)"',re.S) # 无视换行符
# 片名:
findTitle=re.compile(r'<span class="title">(.*)</span>')
# 评分:
findRating=re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')
# 评价人数:
findJudge=re.compile(r'<span>(\d*)人评价</span>')
# 概述:
findInq=re.compile(r'<span class="inq">(.*)</span>')
# 相关内容:
findBd=re.compile(r'<p class="">(.*?)</p>',re.S)
def getData(baseurl):
dataList=[] # 用dataList保存全部信息
for i in range(0,10): # 爬取10次/页
url=baseurl+str(i*25)
html=askUrl(url) # 把获取到的网页源码保存到html变量
# 解析数据:
soup=BeautifulSoup(html,'html.parser')
for item in soup.find_all('div',class_='item'): # 遍历一页内所有item类,每个item类包含一部电影的全部信息
# print(item)
data=[] # 用data保存各个信息
item=str(item) # 防止错误:expected string or bytes-like object
link=re.findall(findLink,item)[0] # 在item中根据正则表达式finLInk返回目标信息列表,提取link
data.append(link) # 把link添加至data
img=re.findall(findImg,item)[0]
data.append(img)
title=re.findall(findTitle,item)
if len(title)==2: # 既有中文名也有外文名
ctitle=title[0] # 提取中文名
data.append(ctitle)
etitle=title[1].replace('/','')
etitle=etitle.replace('\xa0','') # 去掉无关符号
data.append(etitle)
else:
data.append(title[0]) # 提取中文名
data.append(' ') # 为保证生成表格的一致性,外文名处留空
rating=re.findall(findRating,item)[0]
data.append(rating)
judge=re.findall(findJudge,item)[0]
data.append(judge)
inq=re.findall(findInq,item)
if len(inq)!=0:
inq=inq[0].replace('。','') # 去掉句号
data.append(inq)
else: # 概述缺失时留空
data.append(' ')
bd=re.findall(findBd,item)[0]
bd=re.sub("<br(\s+)?/>(\s+)?","",bd) # 用正则替换保守地(0/1)去掉br标签内外的空白符
bd=bd.strip() # 去掉前后空格
bd=bd.replace('\xa0','')
data.append(bd)
dataList.append(data) # 把data放入datalist
# print(dataList)
return dataList
def saveData(dataList,savepath):
dataList=pd.DataFrame(dataList,columns=['详情链接','海报链接','中文名','外文名','评分','评价人数','概述','相关内容'])
dataList.insert(0,column='排名',value=range(1,251)) # 添加排名列
dataList.to_excel(savepath,index=False)
if __name__ == '__main__':
main()
print('complete')
处理完成,效果如下:

主要使用Excel。
① 对电影外文名的有无进行判断标注:
② 对电影概述的有无进行判断标注,分析并计算概述内容所包含的信息组块数:
③ 对【相关内容】项进行数据分离:
处理完成,效果如下:
在实际操作过程中,我们可能会因为某些意外而产生额外的数据需求。比如在本项目中,我们观察到部分电影虽然别名判断为0,但实际上具有外名,也就是说我们在提取这些电影的信息时发生了缺失,从而引发了判断上的错误,这就需要我们重新定位合适的数据源并进行相应的“增删改查”。
当意外发生时,我们通常希望在已处理好的工作表上修改,而非生成另一张未经处理的新表,尤其是在那些生成时间偏长的项目中。当修改完成后,我们通常希望能够保留修改程序的可移植性和可重用性,以达到“高内聚、低耦合”的效果。
鉴于此,我们选择尽可能地不在原程序上动工,而是添加相应的外置修改模块。这种模块并非必要,但却是构建理想模型的一部分。
针对本项目中出现的意外,我们可以通过分析网页源代码来发现问题的根源。分析显示,网页设计者并没有为全部电影添加专门储存外文名的第二个title类,而是将部分电影的外文名直接储存在了other类中,这就导致我们无法只通过title类来提取所有电影的外文名。这种情况多出现在国产电影中。
或许在网页设计者看来国产电影的外文名并不是那么重要,但为了数据的准确性,我们应当采取更加全面的提取方式,将缺失的单元格补上并修正判断结果。由于涉及到既定项目格局下的单元格级别的操作,因此考虑引入openpyxl模块。
修改模块的Python代码如下:
import urllib.request,urllib.error
from bs4 import BeautifulSoup
import re
import pandas as pd
import openpyxl as op
import string
baseurl="https://movie.douban.com/top250?start="
findtitle=re.compile(r'<span class="title">(.*)</span>') # 用于提取片名的正则表达式(作为字典的键)
findother=re.compile(r'<span class="other">(.*)</span>') # 用于提取缺失外文名的正则表达式(作为字典的值)
searchdict={} # 定义字典用于查询
def askurl(url):
head={
"User-Agent":"Mozilla / 5.0(Windows NT 10.0; WOW64) AppleWebKit / 537.36(KHTML, like Gecko) Chrome / 86.0 .4240 .198 Safari / 537.36"
}
request=urllib.request.Request(url,headers=head)
response=urllib.request.urlopen(request)
html=""
html=response.read().decode('utf-8')
return html
def getdata(baseurl):
for i in range(0,10):
url=baseurl+str(i*25)
html=askurl(url)
soup=BeautifulSoup(html,'html.parser')
for item in soup.find_all('div',class_='item'):
item=str(item)
title=re.findall(findtitle,item)[0] # 提取中文名
other=re.findall(findother,item)[0] # 缺失外文名粗提取
ename=''
for o in other:
if o in string.ascii_letters: # 精确提取
ename+=o
searchdict[title]=ename # 存储“中文名:缺失外文名”的键值对
print('start...')
getdata(baseurl)
print('%d complete'%len(searchdict),end='\n\n') # 完成检查器
# print(searchdict)
# 读取处理对象:
wb=op.load_workbook('E:/JupyterNb/source/top250-new%.xlsx')
sheets = wb.sheetnames
wb1 = wb[sheets[0]]
# print(wb1.cell(row=1,column=6).value)
for i in range(1,251):
val=wb1.cell(row=i+1,column=6).value # 遍历外文名列并提取值
if val=='' or val[0:1].isspace() : # 检测空白单元格(空值or白值)
# print(val,'#',i)
key=wb1.cell(row=i+1,column=5).value # 将对应的中文名作为key
wb1.cell(row=i+1,column=6).value=searchdict[key] # 通过key返回value并对空白单元格赋值
wb.save('E:/JupyterNb/source/top250-upgrade.xlsx')
print('ok')
以上是本次修改的主要内容,其它修改内容还包括:
修改完成,最终效果如下:
由此可得:该榜单中的评分数据近似服从正态分布,是许多独立随机因素影响的结果,因此具有一定的客观性和现实性。
| 平均值 | 标准差 | 最小值 | 25% | 50% | 75% | 最大值 | 众数 | 变异系数 | |
|---|---|---|---|---|---|---|---|---|---|
| 评分 | 8.9 | 0.3 | 8.3 | 8.7 | 8.9 | 9.1 | 9.7 | 8.7 | 0.034 |
| 评价人数 | 684680 | 420040 | 106217 | 401426 | 567174 | 851796 | 2646665 | 多个 | 0.613 |
| 年份 | 2001 | 16 | 1931 | 1994 | 2004 | 2011 | 2020 | 多个 | 0.008 |
评分的均值为8.9,四分位点为8.7、8.9、9.1,两两差值为0.2;其中25%位点高出最小值0.4,50%位点即中位数等于均值,75%位点低于最大值0.6;结合标准差为0.3,变异系数为0.034,可以看出该指标具有较强的集中趋势和较弱的离散趋势,各电影间区别不算明显,这一点可以通过箱形图得到进一步验证:
值得一提的是,豆瓣网站内的电影评分总体处于2-10之间,基本服从正态分布,平均分为6.8:
https://zhuanlan.zhihu.com/p/24815577
https://zhuanlan.zhihu.com/p/40645725
在这种背景下,该榜单内的电影评分围绕8.9集中,可以看出评分指标对榜单的入选资格具有较大影响,推测其对榜单内的排名顺位亦具有较大的影响。这点将在后面做进一步验证。
评价人数的均值为70万,标准差却达到了40万,四分位点之间、25%位点与最小值之间的差值以十万计,75%位点与最大值的差值却达到了180万,再结合其高达0.613的变异系数(显著高于评分系数的0.034),可以得出该指标具有很强的离散趋势。
但我们能否据此认为该指标在各电影间的区别很大呢?先看一下相应的箱形图:
从图中可以观察到,该数据集中含有不少离群值,大部分集中在[Q3+1.5IQR]之外,有一个甚至处于[Q3+3IQR]之外(为第一个数据,即排在第一位的那部电影的评价人数),而在最低值至75%位点之间,尤其是25%位点至50%位点之间的数据的离散程度则不是很明显。
在该分析项目中,我们无法剔除这些离群数据,甚至越极端的数据(比如排名第一的电影)越难以忽视。因此我们可以认为,该指标的离散趋势之所以这么强,是因为许多电影的评论人数过于突出,以至于表现出明显的分化趋势。
年份的均值为2001,标准差为16,四分位数为1994、2004、2011,变异系数为0.08,可以说相差不大。
但是年份最早的1931,与25%位点相差了足足63年,可见TOP电影的产出经历了相当一段时间的沉寂期,具体表现见如下条形图:
不难看出,自从1931年诞生了第一部TOP电影开始,TOP电影的产量在近半个世纪内都维持在一个很低的水平。
但在在进入八十年代后,TOP电影的产量开始表现出明显的上升趋势,并在1994年迎来了第一个高峰(诞生了13部TOP电影),之后便进入了高产期,并于2004年和2010再度迎来相同的高峰。1994、2004、2010三个数字也同时构成了年份指标中的三个众数。
评价人数和年份的众数均为多个,但显然年份的众数更有意义,不仅是因为其彰显了电子信息技术的飞速发展在电影行业内的映射,还因为这一指标可以显示出哪些年份的电影占据了TOP榜单中最多的份额,从而为寻找电影的“黄金年代”提供参考。
② 名义指标分析
使用Excel数据透视表进行分析:
国家归属指的是一部电影所属的国家。
我们可以很容易地知道一部电影的制片公司以及制片地区,但却并不总是能顺利地确定一部电影的国家归属,因为这些公司和地区时常不是唯一的。
如果了解详细情况不是必须的话,比较高效的方式是将其单独归为一类并重新赋名。于是我在此规定:倘若一部电影有多个制片国家,则将其“国家归属”指标显示为“多个”。
在“国家”指标项中,多个国家名之间使用空格进行分隔,这为我们判断其“国家归属”是否为“多个”提供了便利。但在无法确定单一国家名称前后是否含有空格的情况下,直接依据空格的有无进行判断显得有些不够谨慎,所以我们可以利用含有通配符表达式的“IF-SEARCH”公式【=IF(ISNUMBER(SEARCH("? ?",P2)),"多个",P2)】来进行判断,这样会更加可靠且更具有普适性。
判断完成后,用数据透视表对“国家归属”项下的各个数据进行计数聚合,得出如下频数表并作图:
图中可以看出,上榜的TOP电影中最多的是美国电影,占比30.8%;其次是中国和日本,分别是14.8%和13.2%;接着是韩国,占比4.4%。另外也有很多TOP电影出自多个国家之手,这种模式的占比为30.4%。
在作进一步判断之前,我们需要明确一点:这些数据能够说明一些国家具有生产优秀电影的绝对能力,但却不足以说明这些国家具有生产优秀电影的相对能力。要想衡量相对能力,还需要了解该国生产的电影总量,并计算出优秀电影数量占总数量的比例。
除了定义本身的支撑外,该结论还基于这样一个逻辑:高绝对产出的背后既可以是“少而精”的质量路线,也可以是“多而平”的数量路线,但相对产出的提高,则唯有依赖“少而精”的质量路线才能得以实现。
外名指的是一部电影除中文名以外的名字。理论上应该每一部上榜电影都有,但实际情况存在例外。
由于已事先对判断结果进行了数值化(0/1),因此我们可以利用Excel数据透视表中的求和项对缺失情况进行数量上的聚合分析:
分析显示,有外名的电影共245部,占比为98%,外名缺失的电影共5部,占比2%。
添加各国上榜电影数量作对比,降序排序后观察可知,外名缺失的电影主要分布在高产国之间,其中有3部为中国电影。
由于数量不多,我们可以直接定位到具体电影。基于“国家(区域细分)”项和“中文名”项对“外名有无”项为0(即外名缺失)的电影进行展开分析,可以看出,外名缺失的电影全部与亚洲相关,分别是:《告白》、《让子弹飞》、《我不是药神》、《色,戒》和《倩女幽魂》。
为何会产生缺失呢?我们以《让子弹飞》为例进行探索:
仔细观察可以发现:在排行榜页面中,该电影的名称区块(也就是网页源代码中的title类和other类)并未显示出外文名,但在详情页面的“又名”项的末位,却显示出了该电影的英文名(即“Let The Bullets Fly”)。
也就是说,所谓的“外名缺失”是展现意义上的缺失,而非实际意义上的缺失,是网页设计的问题,而非值得研究的问题。
经考察,其它四部电影均是如此,这里就不一一赘述了。
概述指的是对某个电影的一句话描述,位于排行榜页面中电影单元的最下方。
不是每个电影都有概述。通过对概述的有无进行判断,我们发现其存在一种减少的趋势。
为了进一步了解情况,我们使用50个为一组的“打包”项来对0/1判断式的“概述有无”项进行求和聚合,其值即为每个分段内包含概述条目的电影的数目:
以打包组为单位,按照排名从小到大排列,可以看出虽然概述缺失的电影只占总体的5.2%,但其随着排名的推后具有比较明显的数量上升趋势(也就是图中所呈现出的非缺失电影的数量下降趋势)。
由于榜单外的电影详情页面中并不存在所谓的概述模块,因此我们有理由认为该模块是在榜单生成后特意添加的,也就是说这一指标虽然和其他外显指标一同展现,但其并不为入选资格的研判提供参考价值。
按照这一思路,我们可以对缺失情况背后的动机作如下推测:概述是为了点出一部作品的亮点从而达到一句话推荐的效果,随着电影排名的推后,电影的质量和曝光都会有所降低,利用一句话概述进行电影推荐的必要性也会有所下降。
类型标签共有22个,表征了一部电影所击中的电影类型。在豆瓣的语境下,一部电影的类型标签通常不是唯一的。
依据0/1式判断标注求出TOP榜单中含有某标签的电影的数量及其占比,做出相关列表和图表并按降序排序:
可以看出,榜单电影的类型标签中最多的为“剧情”标签,含有该标签的电影占总电影比例为75.2%,数量上远高于其它标签;其次是“爱情”、“喜剧”、“冒险”、“犯罪”和“奇幻”标签,占比分别为22.8%、21.6%、18.4%、16.8%和16.4%。
正如前文所言,在不确定总量的情况下,我们无法单纯根据这些比例来推断含有某个标签的电影的上榜可能(具体总结见下方TIPs)。发掘这类数据更多价值的方法之一,是对这类数据和排名进行相关分析,来辨析该数据到底是均匀分散在榜单中的客观指标,还是跟随排名波动而波动的相关指标。
TIPs:
首先通过SPSS相关分析确定各指标与排名数据之间的关联程度,然后选择关联程度较高的组合进行回归分析,依据回归分析的结果推导指标对排名的影响公式。
① 相关分析
使用“排名系数”项代替“排名”项参与相关分析:
| 参与指标: | 评分 | 评价人数 | 概述有无 | 年份 | 国家归属 | 类型标签 |
|---|---|---|---|---|---|---|
| 相关类型 | Spearman/Pearson | Spearman/Pearson | Spearman/点二列/Eta方 | Spearman | Spearman/卡方检验/Eta方 | Spearman/点二列/Gamma/Eta方 |
| 显著性 | 0.00/0.00 | 0.00/0.00 | 0.00/0.00/... | 0.013 | 0.263/0.433/... | 见下表 |
| 相关系数 | 0.749/0.742 | 0.665/0.655 | 0.244/0.244/0.06 | 0.157 | 0.071/.../0.042 | 见下表 |
| 相关判定 | 强 | 较强 | 较弱 | 弱 | 无 | 见下表 |
说明:
由于类型标签数量太多,我们选取数量上较有代表性的前六个标签进行展开分析:
| 类型标签: | 剧情 | 爱情 | 喜剧 | 冒险 | 犯罪 | 奇幻 |
|---|---|---|---|---|---|---|
| Spearman相关 | 0.109,0.084 | -0.039,0.544 | 0.04,0.53 | 0.085,0.18 | -0.043,0.496 | 0.101,0.112 |
| 点二列相关 | 0.109,0.084 | -0.039,0.544 | 0.04,0.53 | 0.085,0.18 | -0.043,0.496 | 0.101,0.112 |
| Gamma | 0.146,0.069 | -0.053,0.55 | 0.056,0.517 | 0.127,0.155 | -0.067,0.492 | 0.157,0.083 |
| Eta方 | 0.012 | 0.001 | 0.002 | 0.007 | 0.002 | 0.01 |
| 相关判定 | 几乎无 | 无 | 无 | 无 | 无 | 几乎无 |
说明:
经过以上分析,我们可以得出以下结论:
我们还可以通过Excel数据透视表对相关指标按类求出排名值的聚合平均数,通过折线图来验证相关趋势。
以“评分”指标为例,作出评分类别和各评分类别下平均排名(非排名系数)的折线趋势图,可以看出随着评分的增大,平均排名呈现出明显的下降趋势,只在9.5评分处出现了一些意外,可能与某些优质但冷门的电影有关。
② 回归分析
根据相关分析的结果,将“评分”、“评价”、“年份”三个指标作为自变量/协变量,将“排名系数”作为因变量,进行包括线性和非线性在内的回归分析。
本次回归分析的目的主要是建立解释模型而非预测模型,对于数据的独立性和正态性要求比较宽松。为了在此基础之上保证准确度,我们采取多种方法来进行拟合,以此探索并验证最适系数。
为了给回归分析提供一定的参考,我们首先利用相关系数与决定系数之间的数学关系求出R方的大小(R方=$r^2$),以此得出排名系数的变异量中能够被各指标与排名系数的关系所解释的比例有多大:
| 评分 | 评价人数 | 年份 | |
|---|---|---|---|
| r | 0.749 | 0.665 | 0.157 |
| R方 | 0.561 | 0.442 | 0.025 |
a. 散点图矩阵
首先建立四个变量的散点矩阵,对变量间的关系进行初步观察:
经过初步观察,可以得出以下结果:
根据观察结果,我们可以对回归分析的目标进行制定:
b. 多变量回归
采用线性方式对变量间关系进行初步估计和研判,各条件上不作严格要求。
为提高估计的准确性,使用多重线性回归和有序Logistic回归两种方式相互对照。
设评分为 $a$,评价人数为 $b$,年份为 $c$。
--- 多重线性回归:
| 评分(a) | 评价人数(b) | 年份(c) | |
|---|---|---|---|
| R方 | 0.740 | ||
| D-W检验 | 0.425 | ||
| 方差分析 | F=233.528,P=0.000 | ||
| 系数(未标准化) | 163.382 | 7.791E-5 | 0.101 |
| 系数(标准化) | 0.601 | 0.453 | 0.022 |
| 系数显著性 | 0.000 | 0.000 | 0.559 |
| 常量 | -1588.651 | ||
| 容差 | 0.703 | 0.769 | 0.754 |
| VIF | 1.423 | 1.301 | 1.327 |
说明:
--- 有序Logistic回归:
我们先以“排名系数”为因变量进行多个协变量的回归分析:
| 评分 | 评价人数 | 年份 | |
|---|---|---|---|
| 伪R方 | 1.000 | ||
| 卡方检验 | P=0.000 | ||
| 拟合优度检验 | P=0.000 | ||
| 平行线检验 | P=0.000 | ||
| 参数估计 | 18.243 | 1.084E-5 | -0.001 |
| 参数显著性 | 0.000 | 0.000 | 0.898 |
对常数项取中位数,得出其回归方程为:【$logit(P)=18.243a+1.084*10^{-5}b-0.001c+165.78$】
表面上看回归分析的各指标都很理想,但显然有些过于理想了。回归线完全拟合,显著性几乎全部为0,一个粗糙的估计式回归模型中出现如此理想的结果,这种情况是很反常的。
问题之根源,就在于数据集的完全分离和拟完全分离。
具体而言:
用数学语言来表述整个过程就是:
设有数据集$(x_i,Y_i),i=1,...,n$:
$x_i'=(x_{i0},x_{i1},...,x_{ip}),x_{i0}\equiv1$,n行$x_i'$组成$n*(p+1)$的矩阵,为$X$;
$\{Y_i:i=1,...,n\}$为相互独立的随机变量,$Y_i$的概率质量函数(probability mass function)为:
$P[Y_i=j|x_i]=\dfrac{e^{(a_j'x_i)}}{\sum^g_{t=0}e^{(a_t'x_i)}},j=0,1,...,g,g\geq1$;
有$a'_j=(a_{j0},...,a_{jp})\in R^{p+1},1\leq j \leq g,a_0\equiv0$,设$a'=(a'_1,...,a'_g)\in R^{(p+1)g}$;
用$j(i)$表示结果$j$,$j(i)\in [0,g]$,则有$Y_i=j=j(i)$。
设$t\in \{0,...,g\}$ \ $\{j(i)\}$;
则$a_{j(i)}'=a_j'=(a_{j0},...,a_{jp})$,$a_t'=(a_{t0},...,a_{tp})$;
则$(a_{j(i)}-a_t)'=(a_{j0}-a_{t0},...,a_{jp}-a_{tp})=A_{1*(p+1)}$;
已知$x_i'=(1,x_{i1},...,x_{ip})$,则$x_i=A_{(p+1)*1}$;
则$(a_{j(i)}-a_t)'x_i=A_{1*1}$;
当存在$a\in R^v$,对于每一个$i=1,...,n$,都有$(a_{j(i)}-a_t)'x_i>0$时(即该向量能够正确分配所有观测值至它们的组),则称该数据集完全分离;
当存在$a\in R^v$,对于每一个$i=1,...,n$,都有$(a_{j(i)}-a_t)'x_i\geq0$时(即该向量能够正确分配几乎所有观测值至它们的组),则称该数据集拟完全分离。
参考文献:
- Adelin Albert, J. A. Anderson. On the Existence of Maximum Likelihood Estimates in Logistic Regression Models. April 1984, Biometrika 71(1).
- Thomas J Santner, Diane E. Duffy. A Note on A. Albert and J. A. Anderson's Conditions for the Existence of Maximum Likelihood Estimates in Logistic Regression Models. December 1986, Biometrika 73(3).
通过分离的完整定义。我们可以推测其在通常情况下的发生原因(条件):
用一句话来概括就是:数据分布越不均衡,分离的情况就越容易发生。
了解了分离的含义和来源后,接下来需要明确的是分离对Logistic回归的影响。
在Logistic回归模型中,两种分离情况都会阻止参数的最大似然估计值的收敛,使得对数似然函数会随着参数的增加而无限趋近于某个固定值。在完全分离情况下,这个固定值为0;在拟完全分离情况下,这个固定值为非0常数。
当对数似然函数随着参数的增加无限趋近于某值而不会下降时,视参数为变量对其求偏导的结果将始终无法为0。也就是说,我们无法求出此时对数似然函数的最大值,也就无法通过最大似然估计来推断线性组合的具体参数。
为了明确目标数据集的详细分离情况,我们先用对各变量组合进行有序Logistic回归考察:
| 协变量: | 三元 | 评分&评价人数 | 评分&年份 | 评价人数&年份 | 评分 | 评价人数 | 年份 |
|---|---|---|---|---|---|---|---|
| 分离诊断 | 完全分离 | 完全分离 | 无 | 无 | 无 | 无 | 无 |
| -2对数似然比 | 0.000 | 0.000 | 2376.913 | 2529.963 | 1358.748 | 2572.836 | 2161.494 |
可以看出:
有序Logistic回归在本质上依然是二项Logistic回归,其转换方式是依次将因变量按不同的取值水平分割成两个等级。当我们以“排名系数”为因变量对三个协变量进行有序Logistic回归时,因为协变量数量过多,因变量合并程度不够的同时又引入了较多的高相关指标(高相关指标会加剧数据分布的不均衡),所以使得整个数据集发生了完全分离或拟完全分离,进而使得迭代(收敛)后的对数似然值实际为0,-2对数似然比(也就是似然比G检验)为0,最大似然估计值不存在。
所以,为了让有序Logistic回归成功进行,我们需要针对分离的发生原因对源数据进行一些预处理:
所以我们接下来的目标就是寻找某种具有较高合并程度的分组变量作为因变量,使得数据集在该因变量的各组间的具有更小的组间差异,进而使得数据集的分布更加均衡,直到能够消除数据分离为止。
观察数据集,发现将数据分成5(*50)组的“打包”项很适合作为新的因变量。为了便于处理,我们使用SPSS的转换功能将“打包”项重新编码为“打包数值”项。该项将文本数据转化为数值,顺序为降序。
接下来,我们将“打包数值”项作为因变量,对“评分”、“评价人数”、“年份”三个协变量进行有序Logistic回归分析。
结果如下:
| 评分 | 评价人数 | 年份 | |
|---|---|---|---|
| -2对数似然比 | 最终:25.487 | ||
| 卡方检验 | P=0.000 | ||
| 拟合优度检验 | 皮尔逊:P=0.000 | ||
| 伪R方 | 0.837,0.872,0.563 | ||
| 参数估计 | 16.475 | 1.178E-5 | 0.009 |
| 参数显著性 | 0.000 | 0.000 | 0.368 |
| 常数项 | 167.676/170.365/173.296/178.567 |
从中可以看出,-2对数似然比不再为0,伪R方等指标更加贴近真实情况,这证明新模型在消除数据分离的影响上已经有了较大的改善。
对常数项取平均,得出该回归方程为:【$logit(P)=16.475a+1.178*10^{-5}b+0.009c+172.476$】(P为二分结果中某结果的后验概率)。
根据各显著性和伪R方结果,可以看出该模型具有较好的拟合度和有效性。
c. 单变量回归
结合散点图,对因变量(排名)与单个自变量建立回归模型,以期进一步探索各指标对TOP排名的影响。
观察“评分-排名”的散点图,推测适用于线性拟合或曲线拟合,且线性拟合的可能性更大。
分别采用线性模型、对数模型和二次模型对变量组合进行回归拟合,结果如下:
| 线性模型 | 对数模型 | 二次模型 | |
|---|---|---|---|
| R方 | 0.551 | 0.553 | 0.561 |
| F值 | 303.985 | 307.045 | 157.922 |
| 显著性 | 0.000 | 0.000 | 0.000 |
三种模型的R方均大于0.25且大于0.5,说明模型解释度较高;F值均显著,说明存在处理效应。
结合图像(第一张图),可以得出:三种回归模型均有一定效度,且在变量的有效范围内差距并不显著(其中线性模型和对数模型几乎完全重合)。
当曲线回归对比线性回归没有明显的优势时,我们直接采用线性回归即可。
我们首先对全部数据进行线性回归,建立回归模型(第二张图)。倘若我们将每次回归分析时残差处于3个标准残差(或估计标准误)之外的值定义为离群值(异常值),持续剔除并重新分析直至不包含任何离群值,这样就能在剔除了5个离群值之后得到一个新的回归模型(即第三张图)。
两种线性回归的分析结果对比如下:
| 线性回归: | 包含离群值 | 剔除离群值 |
|---|---|---|
| R方 | 0.551/0.549 | 0.648/0.647 |
| D-W检验 | 0.9 | 1.089 |
| F值显著性 | 0.000 | 0.000 |
| 系数 | 201.870 | 220.694 |
| 系数显著性 | 0.000 | 0.000 |
| 常量 | -1675.986 | -1840.537 |
可以看出:
据此,我们得出“评分-排名”的回归方程为:
观察“评价人数-排名”的散点图,推测适用于线性拟合或曲线拟合,且曲线拟合的可能性更大。
分别采用线性模型、对数模型和二次模型对变量组合进行回归拟合,结果如下:
| 线性模型 | 对数模型 | 二次模型 | |
|---|---|---|---|
| R方 | 0.429 | 0.450 | 0.462 |
| F值 | 186.568 | 203.271 | 105.937 |
| 显著性 | 0.000 | 0.000 | 0.000 |
三种模型的R方均大于0.25且接近0.5,说明模型具有不错的解释度,其中对数模型和二次模型的解释度相对更高;F值均显著,说明存在处理效应,其中对数模型的F值最大。
观察图像(第一张图),可以看出虽然二次模型在变量范围内的拟合度不错,但由于其在顶端具有拐点,不符合上升的基本趋势,故排除该模型的使用。
剩下的两类模型中,无论是根据参数指标还是图像表现,对数模型都具有明显的优势,故选用对数模型进行回归拟合。
根据参数估计的结果,可以得出回归方程为:【$y=83.762logx-986.141$】
观察“年份-排名”的散点图,没有明显的拟合趋势,其影响方式在一定程度上与随机变量无异。考虑到两变量的低相关性,这种情况并不意外。
此时可以考虑剔除“年份”变量,但若执意将该变量纳入影响模型的话,可以在承认缺乏拟合趋势的前提下,采用最小二乘法寻找一条相对最适的拟合线。
经拟合,该最适回归方程为:【$y=-0.446x+1016.946$】
d. 模型提炼和比对
根据以上分析,我们对与解释变量相关的各系数指标和回归方程进行集中展现:
| 系数指标↓ | 评分 | 评价人数 | 年份 |
|---|---|---|---|
| 相关系数 | 0.749 | 0.665 | 0.157 |
| 决定系数 | 0.561 | 0.442 | 0.025 |
| 线性回归系数-多重 | 163.382 | 7.791E-5 | 0.101 |
| 线性回归系数-多重(标准化) | 0.601 | 0.453 | 0.022 |
| 线性回归系数-单值 | 201.870 >> 220.694 | -0.446 | |
| 曲线回归系数-对数 | 83.762 | ||
| 有序Logistic回归系数-排名系数 | 18.243 | 1.084E-5 | -0.001 |
| 有序Logistic回归系数-打包数值 | 16.475 | 1.178E-5 | 0.009 |
回归方程:
我们的目标是建立各指标对TOP排名的影响模型,简称排名生成模型。为了构建足够多的模型用于验证和比对,我们先对以上信息进行聚合分析。
系数指标分析:
回归方程分析:
经过以上聚合分析,我们可以提炼出三类(总计7种)先验模型:
说明:
接下来,我们使用Excel对这7个先验模型进行效度验证。
我们首先使用公式【=STANDARDIZE(B2,AVERAGE(\$B\\$2:\$B\\$251),STDEV.P(\$B\\$2:\$B\\$251))】对三个自变量进行标准化,然后利用标准化了的变量来生成标准权重模型的排序数值。其它模型则使用原生变量即可。
得到了一系列排序数值后,我们使用RANK函数【=RANK.EQ(H2,\$H\\$2:\$H\\$251)】对数列进行降序排名并附值,所得结果即为模型拟合的排名数据。
为了验证模型效度,我们需要将拟合的排名数据与原生(标准)排名数据进行比对,通过各模型结果与真实值的接近程度来选择最优模型。
比对方法有两种,一是图像观察,二是偏差量化。
就图像观察而言,我们使用柱形图和折线图来生成观察对象。具体来说:
在“排名数据百分比堆积图”中,若数据段(位点集)内的堆积情况较为平稳,占比分布均匀,则说明该段内的模型拟合情况均良好,不用进行比对;若数据段内的堆积情况不稳,占比分布明显不均匀(尤其是标准数据异常),则说明该段内的模型拟合情况存在较大差异,需要重点进行比对。
我们应当尤其关注标准数据的异常位点(有明显的高于或低于表现),具体原因如下:
注意:数据越小,相同差异造成的占比影响就越大,这也是数据集前端部分分布明显不均的原因之一。此时我们只需重点关注标准数据集中明显异常的位点即可,不必把太多精力放在对整段占比分布的分析上。
观察该图可以看出,在标准数据占比较为突出的那几个位点和位点集中(18,37,59,73-79,135,148-157,187-211,223-247),算术模型和加权模型基本表现出了相同的占比趋势,其中算术模型要更加接近;逻辑模型的占比在某些情况下也会趋于相同,但在更多情况下还是和其余模型一起表现出相反的占比趋势。
根据以上观察结果,我们初步判断算术模型和加权模型具有较大优势,逻辑模型则具有一定优势。
在“动态趋势对比图”的各组合中,模型之于标准趋势的波动越明显,其现实拟合性也就越缺乏。
观察该图,可以看出标准权重模型和多因素模型的波动性要明显大于单因素综合模型,其中逻辑模型的波动相对最小;在单因素综合模型中,算术模型的波动性要相对小于加权模型的波动性,且各波动点的共变趋势更强。
根据以上观察结果,我们可以进一步判断算术模型和加权模型具有明显优势,其中算术模型优势更大。
经检验,回归模型和线性模型的趋势线几乎完全相同,说明变量的标准化和这两个模型的结果生成没有问题。
就偏差量化而言,我们使用拟合排名与标准排名的对位差值的平方和来表示“偏差量”,使用平均偏差量的开方来表示“偏差度”,公式如下:
“偏差量”之所以使用差值的平方而不是绝对值,是因为平方不仅能避免正负相抵,还能放大偏差的程度,从而有利于计算结果间的相互比较。
“偏差度”作为数据平方后的开方,量纲和各排名数据保持一致,既可视为对平均偏差的一种放大式替代,亦可以用于对差值分布的描述。
作为一种对模型数据与标准数据之差异的量度,模型的有效性与其偏差量或偏差度的大小成反比,我们的目的即是寻找偏差度较小的那些模型。
经计算,各模型的偏差量化指标如下:
| 相关模型 | 决定模型 | 回归模型 | 线性模型 | 逻辑模型 | 算术模型 | 加权模型 | |
|---|---|---|---|---|---|---|---|
| 偏差量 | 202676 | 204338 | 212112 | 212186 | 167496 | 88410 | 106200 |
| 偏差度 | 28.473 | 28.589 | 29.128 | 29.133 | 25.884 | 18.805 | 20.611 |
可以看出,相关模型、决定模型、回归模型和线性模型的偏差度十分接近,都位于29左右;逻辑模型的偏差度与它们相比下降了一个档次,位于26左右;算术模型和加权模型的偏差度处于最低档次,位于20左右。
尽管同处于最低档位,算术模型的偏差度仍然要比加权模型低2个单位左右。结合前面对图像的观察结果,我们可以得出七个模型中的最优后验模型为单因素综合模型中的算术模型,即:
这便是各指标对TOP排名的最终影响模型。
最后还有几点需要说明:
从1931到2020,近百年的时间内诞生了无数部电影。豆瓣TOP排行榜所展现的,正是其中相对优秀的250部电影。
从数量的角度来衡量,1994、2004和2010三个年份并列第一,但仅仅如此是不足够的。
即便同属于优质电影的范畴,在250个等级顺位面前也不能一概而论。倘若数量相同而质量偏低,那么相应的评价也理应有所不同。
不同顺位之间,优质亦有区别。为了在考虑到数量产出的同时兼顾这种权重性,我们使用年份下排名系数的求和聚合结果来衡量该年份优秀电影产出的整体状况。
若是能藉此方法找出一个在优质电影产出上既有数量又有质量的年份,那么称其为电影的“黄金年份”便也不为过了。
我们使用Excel数据透视表聚合各年份下的排名系数总和与评分总和,以排名系数总和为降序指标进行排序并作图,结果如下:
排名系数聚合项是主要参考指标,评分聚合项是辅助检验指标。对比折线图可以看出,尽管有所波折,评分聚合值在整体上依然随排名系数聚合值的下降而下降,说明作为主要参考指标的排名聚合项是具有一定效度的。
进一步观察折线图,可以看出“1994、2010、2004”三个年份无论就哪个指标而言都具有显著优势,这与数量视角的衡量结果是一致的;在这三个年份中,1994年的两个指标都是最高,且在主要参考指标上尤为突出,高出第二名近20个百分点(在辅助检验指标中,这个数字是2.5)。
根据以上分析结果,我们可以得出电影的“黄金年份”为1994年,具体来说,这是一个诞生了《肖申克的救赎》、《阿甘正传》、《活着》、《这个杀手不太冷》、《低俗小说》、《九品芝麻官》等经典作品的年份。
在之前的数据处理环节中(1.1.2),我们对电影概述部分所包含的信息组块进行了定义(文字为1,标点为0.5),并通过这些定义计算出了一系列具体的组块数值(向下舍入)。
接下来,我们使用Excel数据透视表对各组块数值进行频数聚合,排除组块数为0(即概述不存在)的项并按升序排列,最终结果如下表所示:
根据表中数据绘制组块数类别与频数的折线趋势图,发现该图呈现出较为明显的起伏态势,其中“5-9”、“12-16”、“22”处为明显峰态,其余部分为非峰态或谷态。
对峰态类进行频数统计和频率计算,结果如下表所示:
| 峰态类组块范围 | 分布频数 | 分布频率 |
|---|---|---|
| 5-9 | 75 | 31.6% |
| 12-16 | 70 | 29.5% |
| 22 | 8 | 3.4% |
| 总计 | 153 | 64.6% |
组块数类别共有34类,峰态类包含了其中的11类,约为全类别的三分之一;频数总数为237项,峰态类包含了其中的153项,约为总数的64.6%。
峰态类是数据集中趋势的体现,在该数据集中,三分之一的类别聚合了三分之二的数量。这种集中趋势的产生原因,与人类的短时记忆容量有关。
短时记忆是从感觉记忆到长时记忆的中介环节,其容量又称为短时记忆广度。短时记忆容量的基本信息单位是组块(chunk),组块的数量为7±2(即平均值为7,标准差值为2),这个数据来源于美国心理学家米勒(G.Miller,1956)发表的论文:《神奇的数字7±2:我们信息加工能力的限制》,其信度在各类材料上和各种文化间都得到了证实。
若干短时记忆按时间顺序组合到一起,就形成了名为“工作记忆”的连续加工系统,其容量围绕平均短时记忆容量的倍数而波动,倍数越大,波动程度越小。
组块并非具有绝对大小的单元,而是会随着个人经验变化而变化的结构。倘若抽取长时记忆中的知识经验来对刺激材料进行组织和再加工,就能将若干信息单位联合成更大的组块,从而达到扩充短时记忆容量的目的,因为短时记忆容量的单位始终是组块而非比特。
根据该性质可以得出,该分析项目中的组块定义并不具有在时间跨度上的普适意义。随着短时记忆中藉由内部言语进行加工的复述和存储的循环发生,个体将依据各自的经验储备和认知方式来对组块进行再编码,这就会造成组块数目的特异性变动。
也就是说,该项目中以字符为组块单位的划分方式更多针对的是初次加工概述材料的信息场景。倘若令刺激材料的组块数在7及其倍数的合理范围内波动,就能够在保证内容完整的情况下充分利用短时记忆的暂存容量,从而达到识记轻松和印象深刻的最佳结合点。
通过频数分布表可知,占比最高的“5-9”属于7±2的范畴,其余类别则围绕7的倍数波动(“12-16”在14(7x2)附近,“22”在21(7x3)附近),也就是说全部概述项中有三分之二的项目都符合这种信息需求。
虽然这种特征有利于概述项的信息呈现和加工,但并不一定是有意而为之,还有可能是相关编辑人员在保持工作记忆的状态下流露出了对这种数量结构的天然依赖,亦有可能是相关审核人员在最后的敲定环节中表现出了对这种数量结构的自然青睐。
总而言之,TOP榜单中的概述项具有符合短时记忆和工作记忆容量的信息加工特征,但这种特征的出现并不一定完全依赖于外显的意识。
数据背景:
项目目标:
import pandas as pd
import os
import re
import time
pattern=re.compile(':(.+)\|') # 提取标注有效内容的正则表达式
def agg(path): # 聚合模块:通过文件路径返回聚合信息
df=pd.read_excel(path)
# print(df.columns)
text=df['text']
biaozhu=df['标注']
sumti=df.shape[0]-1 # 文本单元数
sumt=0 # 文本总长度
sumbi=0 # 对标次数
sumb=0 # 标注总长度
for t in text:
# print(str(t))
if str(t)=='nan' or str(t)=='': # 忽略异常项
continue
else:
sumt=sumt+len(str(t)) # t包含float数据不可遍历或使用len
for bz in biaozhu:
# print(str(bz))
if str(bz)=='nan' or str(bz)=='':
continue
else:
result=re.findall(pattern,str(bz)) # 通过正则表达式返回标注的有效内容(对标)
sumbi=sumbi+len(result) # 通过项目树记录对标次数
for r in result:
sumb=sumb+len(r)
return sumti,sumt,sumbi,sumb
def ratindi(path): # 比率指标模块:提取关于有效单元内匹配率的描述性指标
df=pd.read_excel(path)
textbz=df[['text','标注']]
ratlist=[] # 匹配率列表
for tb in textbz.itertuples(): # 按行迭代,析出三元元组
# print(tb[0],tb[1],tb[2])
result=re.findall(pattern,str(tb[2]))
sumr=0 # 储存单元内标注的有效内容长度
for r in result:
sumr=sumr+len(r)
rat=sumr/len(str(tb[1])) # 计算单元内匹配率
# print(tb[0],rat)
if rat!=0 and rat<1: # 将非0且小于100%的匹配率视为有效单元内匹配率
ratlist.append(rat) # 存入ratlist
# print(ratlist)
vali=len(ratlist) # 有效单元数
dsb=pd.Series(ratlist).describe() # 对匹配率列表进行描述性统计
# print(dsb)
# print(dsb[1],dsb[2],dsb[5])
return vali,dsb[1],dsb[2],dsb[5] # 返回有效单元数,mean,std,50%三个指标
database=[]
i=0
for root,dir,file in os.walk(r"E:\语义理解标注\房地产销售流程\Database"):
# 首次遍历结果为子文件夹路径下的各子文件夹名称,2次遍历开始包含子文件内部文件名
for name in file:
data=[] # 临时存储单元
path=os.path.join(root,name)
# print(time.ctime(os.path.getctime(path)))
print(path,agg(path),ratindi(path))
data.append(agg(path)[0]) # 添加文本单元数
data.append(agg(path)[1]) # 添加文本总长度
data.append(agg(path)[2]) # 添加对标次数
data.append(agg(path)[3]) # 添加标注总长度
data.append(ratindi(path)[0]) # 添加有效单元数
data.append(ratindi(path)[1]) # 添加平均匹配率(有效单元内)
data.append(ratindi(path)[2]) # 添加匹配率的标准差
data.append(ratindi(path)[3]) # 添加匹配率的中位数
data.append(path) # 添加文件路径相关信息
database.append(data) # 将一组数据存入database
i+=1
print('[%d complete]'%i)
database=pd.DataFrame(database,columns=['文本单元数','文本总长度','对标次数','标注总长度','有效单元数','平均匹配率','匹配率标准差','匹配率中位数','路径信息'])
database.to_excel('./source/BZdata.xlsx',index=False)
print('complete')
处理完成,效果如下:
处理完成,效果如下:
结算薪资时需要参考文本标注的工作量,标注速度即为工作量和工作时间的比值。通常情况下,我们采用文本总长度来衡量工作量的大小,因为这样最符合客观逻辑,也最容易为人所接受。
但这并不能描述整个标注过程中所有标注人员对工作量的感知,这一点将在后面的章节中加以讨论。就目前对标注速度的计算而言,我们直接使用最普遍的指标即可。
使用SUMIF函数统计某一批次内标注文本的总长度,使用COUNTIF函数统计该批次的总标注天数,两者的比值即为标注的速度(以天为单位)。
速度公式:=SUMIF(Sheet1!$B$2:$B$59,A2,Sheet1!$D$2:$D$59)/COUNTIF(Sheet1!$B$2:$B$59,A2)
具体计算结果见下表:
| 批次 | 标注文本长度 | 标注天数 | 标注速度(字/天) |
|---|---|---|---|
| 1 | 211814 | 10 | 21181 |
| 2 | 70472 | 11 | 6407 |
| 3 | 240893 | 17 | 14170 |
| 4 | 308090 | 20 | 15405 |
可以看出第一批次的标注速度最快,第二批次的标注速度最慢,第三批次和第四批次的标注速度差不多。
文本长度越长,文本中的匹配点越少,文本标注的难度就越大。
因此我们使用匹配率来表示标注难度,匹配率越低,标注难度越大。
已知标注难度 $d$ 为匹配率 $m$ 的函数,且$d(m)$满足以下公理化条件:
不难得出,满足以上公理化条件的函数形式为:$d(m)=-\log m$
代入不同的匹配率,即可得到不同的难度数值。
匹配率分为“平均匹配率”和“加权匹配率”。这两个指标都表示标注文本占总文本的比例,但在具体含义上有所不同。
平均匹配率指的是在排除了无标注单元后,剩下的有效单元中各单元内匹配率的平均值。
加权匹配率指的是以各单元文本长度为权数,以单元文本长度和总文本长度的比值为权重,求各单元内匹配率的加权平均值(在数值上等于标注总长度与文本总长度的比值)。
在熟悉文本后,由于积累了相应的标注经验,可以在很短的时间内筛选出一段文本的标注点并忽略无效单元;由于形成了知觉的恒常性,即便文本形态发生改变也依然能够通过行文环境和自身经验来稳定识别。在这种情况下,各单元文本长度尽管有所不同,但都成为了一个个被识别的组块单位,均具有对于匹配率的基础参考度,因此更适合采用算术平均匹配率(即各单元权重相等的加权平均)。
而在熟悉文本前,由于缺乏经验和知觉的辅助作用,我们需要逐步遍历全部文本并进行地毯式标注对照。在这种情况下,单元的文本长度越长,其对匹配率的参考程度也就越大,因此更适合采用加权平均匹配率(即将全部单元合并后的单元匹配率,此时能够形成对匹配率的完全参考)。
总的来说,两种匹配率都具有其参考价值。之所以选择平均匹配率下的各匹配率进行标准差和中位数的分析,是因为加权匹配率并不存在相应的数据来源。
接下来,我们对两种匹配率指标在各批次内的性质和特征进行描述性统计分析。结果如下表所示:
我们采用均值作为描述集中趋势的指标,公式为:AVERAGEIF(Sheet1!$B$2:$O$59,G3,Sheet1!$H$2:$H$59);
我们采用标准误作为描述离散趋势的指标,公式为:=stdif(Sheet1!$B$2:$O$59,G3,7);
我们采用变异系数作为描述离散程度的指标,公式为:=I3/H3。
由于Excel并未提供类似于SUMIF、AVERAGEIF、COUNTIF等可以对取值条件进行定义的标准差计算函数,因此我们使用VBA代码来编写符合要求的自定义函数stdif来用于标准误的计算:
vb
Function stdif(Arr As Range, m As Integer, n As Integer) '自定义stdif函数(范围,条件,位置)
Dim v(1 To 100) As Variant '一维数组变量
For i = 1 To Arr.Rows.Count '遍历首列单元,提取符合条件的固定位置元素
If Arr.Cells(i, 1) = m Then
v(i) = Arr.Cells(i, n)
End If
Next
stdif = WorksheetFunction.StDev_P(v) '计算标准差
End Function
为了方便数据的观察和分析,我们作出各批次在两种匹配率指标下的折线数据图:
根据图像可以看出,四个批次在两种匹配率指标下具有相似的变动趋势,可以结合两种指标对各批次的标注难度进行综合分析:
第一批次的匹配率均值最低,标准误最低,变异系数偏低,说明该批次的匹配率要显著低于其它批次,且其内部差异最不明显,也就是说其匹配率的低下不仅具有显著性,还具有普遍性;
第二批次的匹配率均值最高,标准误偏高,变异系数偏低,说明该批次的匹配率要显著高于其它批次,且其内部差异并不算特别明显;
第三批次的匹配率均值一般,标准误一般,变异系数一般,说明该批次的匹配率和内部差异都不算突出;
第四批次的匹配率均值一般,标准误偏高,变异系数最高,说明该批次的匹配率虽然不算突出,但其内部差异要显著高于其它批次,因此均值的代表性也最差。
根据以上分析,我们认为第一批次项目的标注难度最高,第二批次项目的标注难度最低,第三批次项目的标注难度略低于第四批次(胜在稳定)。
为验证哪个匹配率指标更符合这一结果,我们计算出各项目的两种标注难度后在批次上对其均值进行聚合,得出结果如下所示:
可以看出,与结论最接近的难度指标是算术难度。虽然加权难度的差别并不算大,但相比之下我们最好使用算术匹配率来参与标注难度的计算。
计算标注速度时,我们使用了文本总长度来表示标注工作量的大小,因为这种衡量方式具有客观普遍性。
但普遍性不等于唯一性。理论上,我们有五种指标可以用来衡量工作量,分别为:文本单元数、文本总长度、对标次数、标注总长度和有效单元数。它们从各自的角度出发表达了对“工作量”这一概念的理解,具有相对客观性和理论存在性,“文本总长度”是其中最具代表性的一个。
① 信息熵
既然这五种指标都具有一定的客观依据,但又不能充分描述被描述对象的状况,那我们至少应该找到某种方法,使得我们对其各自的描述能力能有一个量化的、具体的感知。
指标的描述能力与数据集的不确定性有关。数据分布的差异越大,数据集的平均不确定性越低,越能体现该指标对各数据项的区分度,即说明其描述能力越强。
为了衡量这种性质,我们可以计算各指标项下数据集的信息熵。在此之前,先要消除组间差异中由量纲所决定的那一部分。
观察这五个指标的量纲和方向,结果如下表所示:
| 文本单元数 | 文本总长度 | 对标次数 | 标注总长度 | 有效单元数 | |
|---|---|---|---|---|---|
| 量纲 | 单元格 | 字符 | 标注项 | 字符 | 单元格 |
| 方向 | 正向 | 正向 | 正向 | 正向 | 正向 |
各指标量纲不统一且方向都为正向,因此我们可以使用正向归一化来消除量纲间的差异。Excel公式如下:
=((Sheet1!C4-MIN(Sheet1!C$2:C$59))/(MAX(Sheet1!C$2:C$59)-MIN(Sheet1!C$2:C$59)))+0.00001接下来我们计算各数据项在数据集中出现的概率$p(x_i)$,即每单位指标分配到各数据项下的概率。公式如下:
=A2/SUM(A$2:A$59)得到概率后,通过$H(X)=E[I(x_i)]=-\sum_{i=1}^np(x_i)logp(x_i)$即可计算信息熵。其中$I(x_i)=-logp(x_i)$代表的是某个事件发生(即某个数据项出现)时所包含的信息量,当$p(x_i)\rightarrow1$时,$I(x_i)\rightarrow0$;当$p(x_i)\rightarrow0$时,$I(x_i)\rightarrow\infty$。公式如下:
=-SUMPRODUCT(F2:F59,LOG(F2:F59,2))信息熵越抵,平均信息量(以概率为权的加权平均)越低,为消除不确定性所平均需要了解的信息量就越少,即平均不确定性越低。正如前文所言,平均不确定性能够体现指标的描述能力,也就是说指标的信息熵越抵,其描述能力便越强。
根据以上分析,最终五个指标的信息熵和描述能力如下:
| 文本单元数 | 文本总长度 | 对标次数 | 标注总长度 | 有效单元数 | |
|---|---|---|---|---|---|
| 信息熵 | 5.247 | 5.403 | 5.536 | 5.611 | 5.534 |
| 描述能力 | 5 | 4 | 2 | 1 | 3 |
可以看出,就信息熵而言,五个指标中描述能力最强的是“文本单元数”,其次是“文本总长度”。
文本总长度因其客观性和认可度已被普遍用于标注工作量的计算,根据信息熵的计算结果可以看出其对不同项目的描述能力也十分优秀。那么文本单元数呢?其作为一个在描述能力上甚至要优于文本总长度的工作量计算指标又有什么含义和意义呢?
通过对熟练标注作业进行经验分析,我们可以发掘出更多关于标注工作的细节。
在接手标注文件的初期,由于对文本内容和标注内容不甚熟悉,逐字遍历文本后与标注内容对照几乎是工作的常态。在这一阶段,用文本总长度来表征工作量是很合理的。
在处理标注文件的后期,由于已对标注内容十分熟悉,对文本结构和内容也有了明显预期,因此会在工作模式上产生以下变化:
所以在这一阶段,用文本单元数来表征工作量是更为合适的。
总得来说,文本总长度和文本单元数是衡量工作量的两个理想指标,其中文本总长度是客观的、偏前期的,文本单元数则是主观的、偏后期的。
② 因子分析
因子分析通过对载荷矩阵施加因子轴旋转,扩展了主成分分析在专业上的可解释意义。具体而言,因子分析在提取公因子时不仅考虑了相关关系的存在,还考虑了相关关系的强弱,使得提取出来的公因子既能够起到降维作用,又能够很好地被已知变量所解释。
我们使用SPSS对五种工作量指标进行因子分析,具体过程如下:
首先对分析条件进行判断。KMO检验的值为0.651>0.6>0.5,说明偏相关系数平方和较小,简单相关系数平方和较大,原始数据中存在公共因子;巴特利特球形度检验的显著性P=0.000<0.05,说明相关系数矩阵为非单位矩阵,各变量间不完全独立,具有一定的相关性。也就是说,该组变量适合进行因子分析。
公因子方差又称共同度,为各变量在各公因子上载荷的平方和,表示变量中的信息(方差)能够被公因子所解释的程度。可以看出倘若视变量包含信息为1,在进行公因子提取后,被表达的信息值在0.802到0.929之间,均大于0.7,说明各变量均能够很好地被公因子所表达,信息降维的质量很高。
在总方差解释表中,我们主要关注旋转后的载荷平方和。基于特征值大于1的标准,我们提取出两个公因子,特征值分别为2.919和1.349,其占总特征值的百分比即为解释的总方差比率。可以看出,筛选出来的两个公因子共同解释了总变异的85.376%,保留了较多的原始变量信息,因此无需额外添加特征值。
各特征值所解释的方差占累积解释方差的百分比即为特征值的方差贡献率,可视为公因子的权重参与到综合因子的计算中。
碎石图又名滑坡图,坡度越高的点重力势能越大。倘若某点之后的坡度趋于平缓,即斜率在该点处显著下降,则选取该点以上的特征值参与到因子分析中。观察图像可知,可选的特征点共有两个,符合特征值大于1的标准,我们将其命名为公因子1与公因子2。
成分矩阵是旋转前的载荷矩阵,通过相关系数来表征变量对公因子的依赖程度。该矩阵可以通过除以对应主成分的特征根来消除主成分的权重不平等,所得矩阵即为主成分分析的系数矩阵。
旋转后的成分矩阵是因子分析所需要的载荷矩阵,可以看出“对标次数”、“标注总长度”、“有效单元数”在公因子1上有较高载荷,“文本总长度”、“文本单元数”在公因子2上有较高载荷,因此可以把公因子1解释为“标注工作量”,其处理对象为“标注”,处理方式为“对标”;把公因子2解释为“文本工作量”,其处理对象为“文本”,处理方式为“阅读”。也就是说工作量主要分为两个部分,一是浏览文本,二是对标。
这些性质也体现在旋转后的空间载荷图中。观察图像可以看出,5个因子在坐标系内聚成两类,围绕纵轴的两个因子在公因子2上具有较大载荷,围绕横轴的三个因子则在公因子1上具有较大载荷,各坐标点与旋转后的成分矩阵一一对应。根据图像,我们可以很直观地看出公因子1与公因子2主要代表了哪些变量。
值得一提的是,虽然两个公因子在载荷分布上具有明显的倾向性,但它们之间并不是完全孤立的。变量在某公因子上有较大载荷的同时也在另一个公因子上有一定载荷,具体的相对关系和影响方式可以在后面的表达式中得到展现。
成分得分系数矩阵是生成表达式并计算公因子得分的主要依据,析出的方法为回归。
设“文本单元数”、“文本总长度”、“对标次数”、“标注总长度”、“有效单元数”标准化后的变量为$x_{1-5}$,公因子1和公因子2为$F_1$、$F_2$,可以得出公因子的得分表达式为:
可以看出对各公因子影响较大的是在其上有较大载荷分布的变量(即归属于各解释类别的变量),但在其上载荷分布较小的变量也并非完全没有影响,其中比较明显的是 $F_2$ 中 $x_4$ 的系数-0.231(且该变量在公因子2上的载荷也为负值)。
该系数说明标注长度越长,浏览文本所花费的时间也会相对减少,其中介变量可能是匹配率(标注/文本)。根据上文可知,匹配率的增加会降低标注难度($d(m)=-\log m$),从而减小浏览文本的负担。而标注长度越长,匹配率增加的概率也会越高。
两个公因子可以合并成一个综合因子,综合因子得分即为对完整工作量的描述(解释为“综合工作量”)。
合并时需要考虑各公因子权重,权重系数来源于总方差解释表中的旋转载荷平方和。尽管两个公因子的累积方差解释率为85.376%,但由于我们在实际研究中默认两个公因子即代表了全部的变量,因此视两公因子的总方差解释率为100%,各公因子权重即是其方差解释率(贡献率)的归一化系数。
设综合因子为$F$,其表达式为:
接着我们将变量系数矩阵(5x2)和公因子权重矩阵(2x1)相乘,得出系数矩阵C(5x1),为各变量在综合表达式中的系数。
数组公式为:{=MMULT(M2:N6,M8:M9)},结果如下:
据此得出 $F$ 的最终表达式为:$F=0.188x_1+0.149x_2+0.213x_3+0.148x_4+0.225x_5$,$F$ 为标准化了的数值。
对C进行归一化处理即可得到各变量的权重W,对C下各公因子内主变量系数分别进行归一化处理即可得到各变量的内权重W1(假设变量系数完全来自于公因子)。
设两公因子权重相等,利用数组公式{=0.5*(M2:M6+N2:N6)}可以得到系数C0,对系数C0全部进行归一化处理即可得到变量的等权权重。
将各权重整理成表格,结果如下:
| 文本单元数 | 文本总长度 | 对标次数 | 标注总长度 | 有效单元数 | |
|---|---|---|---|---|---|
| 权重W | 0.204 | 0.161 | 0.231 | 0.160 | 0.244 |
| 内权重W1 | 0.558 | 0.442 | 0.363 | 0.253 | 0.384 |
| 等权权重W0 | 0.244 | 0.213 | 0.201 | 0.112 | 0.229 |
与变量的信息熵表对比可以看出,在不考虑公因子综合的情况下,因子分析下的权重与信息熵所代表的描述能力有着一样的相对关系;在不考虑公因子权重的情况下,因子分析下的权重与信息熵所代表的描述能力有着近似的相对关系。
这种信息熵与权重的关系,为熵值法的存在提供了一定的理论依据。
综上所述,我们对于标注工作量的描述可以采取以下方法:
“文本质量”是一个具有综合性质的指标,可以从两个角度来衡量,一是“有效率”,二是“均匀度”。
有效度:有效单元数占总单元数的比例,用“有效单元数”与“文本单元数”的比值来表示。有效度越高,标注文本所占用的时间比例越高,审阅无标注文本所占用的时间比例越少,因此文本质量越高。
均匀度:有效单元内匹配率的均匀程度(即匹配率的离散趋势的相反),用“平均匹配率”与“匹配率标准差”的比值(即决定系数的倒数)来表示。均匀度越高,标注节奏越平稳,越能说明文本单元的划分程度合理,因此文本质量越高。
分别求出两个质量指标在每个项目下的对应结果,更新后的表格如下图所示:
为了验证各批次的文本质量,我们利用数据透视表对各批次下的文本质量相关指标进行平均聚合并作图,结果如下所示:
无论从哪个指标来说,第二批次的文本质量都是最高。但就其它批次而言,我们还无法得出准确的相对关系。
为了得出更准确且更有效的结论,我们对“有效度”和“均匀度”这两个共同描述文本质量的指标进行客观赋权法,以期降维出一个能够起到综合评价作用的“文本质量”指标。
设有效度为 $a$,均匀度为 $b$,文本质量为 $y$。可采用的降维方法有变异系数法、熵值法和主成分因子分析法,接下来对它们逐一进行分析:
① 变异系数法
变异性越大,数据点间差异越大,数据分布的离散程度越高,数据集的信息量(方差)越大,特征和区分度越明显,其在多组数据集内的权重也就越大。
变异系数法的本质是比较数据集的离散程度。如果量纲完全一致,便可以直接使用标准差来衡量;如果量纲不一致,则使用消除了量纲差异的变异系数来衡量。
变异系数的Excel计算公式为=STDEV.P(T$2:T$59)/AVERAGE(T$2:T$59),各指标权重大小为其变异系数的占比。
计算结果如下表所示:
| 有效度 | 均匀度 | |
|---|---|---|
| 变异系数 | 0.669 | 0.308 |
| 权重 | 0.685 | 0.315 |
通过加权求和得出降维公式:$y=0.685a+0.315b$
② 熵值法
熵值法通过信息熵来确定权重。信息熵越小,随机性越小,平均信息量越少,不确定性越小,为消除不确定性所需要的信息量越少,就最大离散熵而言所提供的信息量越大,因此信息集的权重也就越大。
为排除量纲差异所带来的影响,需要先对数据进行无量纲化处理。经比较,极值熵值法为熵值法中的最优改进类型(朱喜安,魏国栋. 熵值法中无量纲化方法优良标准的探讨[J]. 统计与决策,2015(2)),故选用极值处理法(属于归一化的一种)来进行数据的去量纲化。
已知指标均为正向,则极值处理法的公式为:
=(T2-MIN(T$2:T$59))/(MAX(T$2:T$59)-MIN(T$2:T$59))+0.00001计算各项目样本在指标下的出现概率,公式为:
=V2/SUM(V$2:V$59)使用$E_j=-\dfrac{1}{\ln n}\sum\limits_{i=1}^np_{ij}\ln{p_{ij}}$计算各指标的归一化信息熵,公式和说明如下:
=-SUMPRODUCT(X2:X59,LN(X2:X59))/LN(COUNTA(X2:X59))为使信息熵与权重呈反比,我们使用$W_j=\dfrac{1-E_j}{\sum_{j=1}^m(1-E_j)}(j=1,2,...,m)$来计算权重,公式如下:
=(1-X60)/(COUNTA($X$60:$Y$60)-SUM($X$60:$Y$60))最终求得两个指标的信息熵和权重如下表所示:
| 有效度 | 均匀度 | |
|---|---|---|
| 信息熵 | 0.915 | 0.957 |
| 权重 | 0.665 | 0.335 |
降维公式:$y=0.665a+0.335b$
可以看出数据集的变异性与信息熵紧密相关。
③ 因子分析
当变量数量为两个时,简单相关系数等于偏相关系数,即$\sum\sum_{i\neq j}r_{ij}^2\equiv\sum\sum_{i\neq j}r_{ij.k}^2$,KMO值恒等于0.5,因此不适合进行主成分分析与因子分析。
综上所述:
变异系数法求得的结果与熵值法很接近,因此我们选用更加严谨的熵值法来确定文本质量的降维公式,即:$y=0.665a+0.335b$
其中有效度为 $a$,均匀度为 $b$,文本质量为 $y$,均为无量纲数据。
值得一提的是,只有共同描述同一性质的指标才可以合并,分别描述同一性质的指标则不可合并,仅可比较。这便是我们对描述标注难度的两种匹配率不进行合并(而只进行比较)的主要原因。
根据上文所求公式计算“算术难度”、“综合工作量”和“文本质量”,所得表格如下所示:
我们使用Pearson相关来计算各变量间的相关系数,具体原因如下:
经过SPSS分析和Excel转换,最终的相关矩阵表如下所示:
通过相关矩阵,我们可以很清晰地看出各变量之间的相关关系。
根据相关关系中的相关系数,我们既可以对已得出的公式进行验证,也可以为需要解决的问题提供参考。
标注速度以天为单位,描述对象为批次,分析影响因素时也需要将变量聚合到各个批次上。
从逻辑上来讲,如果自变量是文件属性,那么文件处理速度便是因变量,即因果关系是确定的。在这种情况下,我们只需要通过相关系数来确定影响的程度和方向即可。
一个变量所引用的其它变量越多,则该变量越具有代表性。根据这一标准,我们选择最具代表性的“综合工作量”、“文本质量”和“算术难度”三个变量来参与相关分析,批次内聚合方式为取平均,结果如下:
可以看出,算术难度与标注速度之间存在很高的正相关(0.903),文本质量和标注速度之间存在很高的负相关(-0.878),偏相关分析结果亦是如此。
根据已知因果关系,我们可以说,文本难度越大,标注速度越快;文本质量越高,标注速度越慢。其中介变量为匹配率,即标注点的密集程度,标注点越密集,对标所耗费的时间就越多,处理的文本总长度就越少,反之亦然。
这种反直觉的结论证明了只用文本总长度来衡量工作量的缺陷。如果工作量同时考虑了浏览文本和对标,那便有可能出现难度与速度成负相关(-0.4),质量与速度成正相关(+0.4)的情况:
综合工作量与标注速度之间的相关关系较弱(-0.326),其原因可以通过偏相关分析来探索:
因此我们可以说,综合工作量与标注速度之间并不存在直接的联系,它在某些情况下表现出来的与标注速度的共变性主要来源于其它变量(尤其是算术难度)的影响,且这种影响是单向的。
这一结论也为“综合标注速度”这一因变量指标的存在提供了理论依据。
自动化相关表可以根据选择的变量自动生成对应的相关系数:
首先利用数据验证建立所有变量的下拉列表,然后通过变量值返回相关系数。
我们用match函数获取单元格内容的序列号,用index函数返回对应序列号下的整列数据,用correl函数求两列数据的Pearson相关。
公式如下:
excel
=CORREL(
INDEX(Sheet1!$C$2:$AC$59,0,MATCH(相关表!Q2,Sheet1!$C$1:$AC$1,0)),
INDEX(Sheet1!$C$2:$AC$59,0,MATCH(相关表!R1,Sheet1!$C$1:$AC$1,0))
)
将去重后的日期列作为数据验证的序列建立下拉列表,即可查看不同周期下的内容表现:
自动化功能依赖于函数和条件格式,下面将对此一一进行展现。
周报标题的单位为“年月周”,各单位数值随起始日期的变动而变动。公式如下:
excel
=YEAR($B$1)&"年"&MONTH($B$1)&"月第"&ROUNDUP(DAY($B$1)/7,0)&"周"
项目进度为已完成的工作量,计算的是终止日期前的文本总长度,公式如下:
excel
=SUMIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
"<="&$D$1,
INDEX(Sheet1!$A:$AC,0,MATCH(A$8,Sheet1!$A$1:$AC$1,0))
)
项目进度项中采用了基于数据条的条件格式,其值为项目进度占总目标的百分比,范围为$[0,1]$。
总目标可以是计算全部的工作量(项目完成后),也可以是一个固定值(项目完成前)。若是计算,公式如下:
excel
=SUM(INDEX(Sheet1!$A:$AC,0,MATCH($A$8,Sheet1!$A$1:$AC$1,0)))
结果指标包含可变动的日期和各维度下的一系列工作成果,若当日没有项目内容则显示“无”,若有多个项目内容则求其总和。公式如下:
excel
=IF(
COUNTIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),$A14
)<>0,
SUMIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),$A14,
INDEX(Sheet1!$A:$AC,0,MATCH(C$13,Sheet1!$A$1:$AC$1,0))
),
"无")
聚合栏计算的是一个周期内的工作成果的总和。
结果指标表中采用了基于公式判断的条件格式,低于平均的单元格值将显示为红色。基础公式为=C14<AVERAGE(C$14:C$18)。
特征指标包含可变动的日期和各维度下的一系列项目特征,若当日没有项目内容则显示“无”,若有多个项目内容则求其平均。公式如下:
excel
=IF(
COUNTIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$23,Sheet1!$A$1:$AC$1,0)),$A24
)<>0,
SUMIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$23,Sheet1!$A$1:$AC$1,0)),$A24,
INDEX(Sheet1!$A:$AC,0,MATCH(C$23,Sheet1!$A$1:$AC$1,0))
)/
COUNTIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$23,Sheet1!$A$1:$AC$1,0)),$A24
),
"无")
聚合栏计算的是一个周期内的项目特征的平均。
周统计直接引用聚合栏中的结果,并展现相应指标在一周内的波动趋势。
周环比分为结果指标的周环比和特征指标的周环比。环比增长率=(本期数值-上期数值)/上期数值×100%=(本期数值/上期数值-1)×100%。
结果周环比的上期数值为对应指标的总和,公式如下:
excel
=A$9/
SUMIFS(
INDEX(Sheet1!$A:$AC,0,MATCH(A$8,Sheet1!$A$1:$AC$1,0)),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
">="&($B$1-7),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
"<="&($D$1-7)
)-1
特征周环比的上期数值为对应指标的平均,公式如下:
excel
=E9/
SUMIFS(
INDEX(Sheet1!$A:$AC,0,MATCH(E8,Sheet1!$A$1:$AC$1,0)),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
">="&($B$1-7),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
"<="&($D$1-7)
)*
SUMPRODUCT(
(INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0))>=$B$1-7)*
(INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0))<=$D$1-7)
)-1
这里使用sumproduct函数输出两种判断条件下逻辑值的乘积和,数值上等于对应指标的个数。
周环比项中采用了基于单元格值的条件格式,大于0显示绿色字体和图标,小于0显示红色字体和图标,等于0显示黄色感叹号。
自动化特征分布图可以根据不同的数据源绘制相应的九宫格,点击更新按钮即可完成图像的刷新。
九宫格布局将二维变量定序划分为三段,生成共计九种特征类别。依据图像的数据分布情况即可对不同指标下项目的总体特征进行研判。
下图采用的是“算术难度”和“文本质量”两个指标:
实现方法如下:
基于数据验证建立各指标的下拉列表,动态提取各指标值作为数据源(不受源数据填写顺序的影响)。
公式如下:
excel
=INDEX(Sheet1!$A:$AC,ROW(C2),MATCH(C$1,Sheet1!$A$1:$AC$1,0))
效果如下:
利用max函数和min函数计算数据源的三分位点,绘制趋势图用于验证,效果如下:
依据数据源建立散点图,添加三分之一位点和三分之二位点为新的系列,将它们的误差线作为九宫格的内部划分线。
各点坐标可以随数据源变动而变化,坐标轴范围和标题的变化则需要用到宏代码。代码如下:
vb
Sub upgrade()
' upgrade 宏
' min,max,name
' 快捷键: Ctrl+u
ActiveSheet.ChartObjects("图表 2").Activate
ActiveChart.Axes(xlCategory).Select '选中水平轴
ActiveChart.Axes(xlCategory).MinimumScale = [G5] '更新轴最小值
ActiveChart.Axes(xlCategory).MaximumScale = [G2] '更新轴最大值
ActiveChart.Axes(xlCategory).AxisTitle.Select '选中水平轴标题
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = [G1] '更新轴标题
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = [H5]
ActiveChart.Axes(xlValue).MaximumScale = [H2]
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = [H1]
End Sub
将宏代码固定到表单控件,叠放次序设置为顶层。
重新选择研判指标后数据源自动发生变化,之后点击“更新”按钮即可完成图表的全部刷新。
效果如下:
为了获取更多观察信息,我们将批次列设为系列数据标签的值选择范围,在数据点右边生成关于其所属批次的标注。
观察前后两图可以看出,第二批次的项目在标注量大的同时文本量小,质量高的同时难度较低,内容价值高且处理较轻松,是比较理想的项目类型。
首先将源表设置为超级表,以满足新任务的实时添加和呈现。
依据项目批次提取起止时间,核心逻辑为用match函数提取各批次起止点处的序列号,依据序列号返回日期列中的对应值,提取方式不受源数据填写顺序的影响。
公式如下:
excel
开始日期(精确匹配):
=INDEX(
INDEX(Sheet1!$A:$AC,0,MATCH("日期",Sheet1!$A$1:$AC$1,0)),
MATCH($C2,
INDEX(Sheet1!$A:$AC,0,MATCH("批次",Sheet1!$A$1:$AC$1,0)),
0))
结束日期(升序匹配):
=INDEX(
INDEX(Sheet1!$A:$AC,0,MATCH("日期",Sheet1!$A$1:$AC$1,0)),
MATCH($C2,
INDEX(Sheet1!$A:$AC,0,MATCH("批次",Sheet1!$A$1:$AC$1,0)),
1))
对“开始日期”和“需要天数”建立堆积条形图,消除“开始日期”的颜色并将条形图倒置,调整坐标轴起始点后所呈现图像为进度条的整体框架。
为“开始日期”添加正偏差方向的水平误差线,误差量设置为“已完成天数”,调整各显示参数后所得图像即为完整的项目进度条。
“项目进度”中内容用于文字标注,其呈现公式为:
excel
`="项目进度:"&ROUND([@已完成天数]/[@需要天数]*100,0)&"%"`
通过support列的数据标签将标注放置在图像中。为满足即时更新需要,值选择范围为=甘特图!$H$2:$H$30。
演示:
假设我们开始处理第5批次的项目,计划好总项目及子项目的完成时间后陆续将相关数据写入源数据工作表中:
在超级表的“批次”列下方填入数字5,敲击回车后在“已完成天数”列下方填入7(假定),即可完成甘特图的全部更新:
为保证辅助列的同步生成,其值并非为单纯的数字1,而是对应批次列值的最小因数。
头部CSS:
%%HTML
<style>
li a:hover {
background-color: #111;
}
</style>
--END--